#!python3
# 使用 MySQL 官方提供的 mysql-connector 驱动器操作数据库

import mysql.connector

#  创建数据库连接
db = mysql.connector.connect(
    host='localhost',
    port='3306',
    user='root',
    passwd='root',
    database='runoob_db'
)


# print(db)


def createDatabase(dbname):
    """
    创建数据库
    :return:
    """
    try:
        myCursor = db.cursor()
        myCursor.execute("SHOW DATABASES")

        lst = []
        for cursor in myCursor:
            lst.append(str(cursor[0]).upper())

        if dbname.upper() not in lst:
            myCursor.execute('create database %s' % dbname)
            print('创建数据库%s成功 # %s' % (dbname, myCursor))
        else:
            print('创建数据库%s失败:数据库已存在' % dbname)
    except mysql.connector.errors.DatabaseError as e:
        print('创建数据库%s失败：' % dbname + e.msg)


def dropDatabase(dbname):
    """
    删除数据库
    :return:
    """
    try:
        myCursor = db.cursor()
        myCursor.execute('drop database %s' % dbname)
        print('删除数据库%s成功 # %s' % (dbname, myCursor))
    except mysql.connector.errors.DatabaseError as e:
        print('删除数据库%s失败：' % dbname + e.msg)


def showTables():
    """
    查看数据库中的数据表
    :return:
    """
    try:
        myCursor = db.cursor()
        myCursor.execute('show tables')
        for table in myCursor:
            print(table[0])
    except mysql.connector.errors.DatabaseError as e:
        print('获取数据表失败：' + e.msg)


def createTable(createTableSql):
    """
    创建数据表
    :return:
    """
    try:
        myCursor = db.cursor()
        myCursor.execute(createTableSql)
        print(myCursor)
    except mysql.connector.errors.DatabaseError as e:
        print('执行%s失败：' % createTableSql + e.msg)


def insertData(sql, data):
    """
    插入单笔数据
    :param sql:
    :param data: 元组
    :return:
    """
    myCursor = db.cursor()

    myCursor.execute(sql, data)

    db.commit()  # 数据表内容有更新，必须使用到该语句

    #  在数据记录插入后，获取该记录的主键 ID
    rowid = myCursor.lastrowid

    print("记录插入成功,记录数%d, id = %s" % (myCursor.rowcount,  rowid))


def insertDatas(sql, datas):
    """
    插入批量数据
    :param sql:
    :param datas: 包含元组的列表
    :return:
    """
    myCursor = db.cursor()

    myCursor.executemany(sql, datas)  # 此处插入多笔数据与插入单笔数据方法有区别

    db.commit()  # 数据表内容有更新，必须使用到该语句

    print("记录插入成功,记录数：%d" % myCursor.rowcount)


def queryData(sql, conditions):
    """
    查询数据
    :param sql:
    :param conditions: 元组
    :return:
    """
    myCursor = db.cursor()
    myCursor.execute(sql, conditions)
    result = myCursor.fetchall()  # fetchall() 获取所有记录

    print(result)
    for row in result:
        print(row)


if __name__ == '__main__':
    # createDatabase('runoob_db')
    # dropDatabase('runoob_db')

    # sql = 'CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))'
    # createTable(sql)
    # showTables()

    # sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
    # data1 = ("RUNOOB", "https://www.runoob.com")
    # data2 = ("Zhihu", "https://www.zhihu.com")
    # insertData(sql, data1)
    # insertData(sql, data2)

    # datas = [
    #     ('Google', 'https://www.google.com'),
    #     ('Github', 'https://www.github.com'),
    #     ('Taobao', 'https://www.taobao.com'),
    #     ('stackoverflow', 'https://www.stackoverflow.com/')
    # ]
    # insertDatas(sql, datas)

    sql = "SELECT id, name, url FROM sites where id >= %s and id <= %s order by name desc"
    conditions = (2, 5)
    queryData(sql, conditions)

    # 删除更新表数据与上面的方法类似，不再举例
